package com.example.lin.day8_30.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.example.lin.day8_30.pojo.Student;

import java.util.ArrayList;
import java.util.List;

/**
 * 创建管理数据库
 */
public class HelloWorldOpenHelper extends SQLiteOpenHelper {


    private static final String TAG = "HelloWorldOpenHelper";

    /**
     * 数据库名称
     */
    private static final String DB_NAME = "HelloWorld";
    /**
     * 当前数据库的版本号
     */
    private static final int DB_VERSION = 6;

    /**
     * 表名
     */
    private static final String TABLE_NAME = "student";
    /**
     * 字段名
     */
    public static final String KEY_ID = "_id";
    public static final String KEY_NAME = "sName";
    public static final String KEY_AGE = "sAge";
    public static final String KEY_HEIGHT = "sHeight";
    public static final String KEY_SEX = "sSex";


    private static HelloWorldOpenHelper instance;

    public static HelloWorldOpenHelper getInstance(Context context) {
        if (instance == null) {
            synchronized (HelloWorldOpenHelper.class) {
                if (instance == null) {
                    instance = new HelloWorldOpenHelper(context);
                }
            }
        }
        return instance;
    }


    private HelloWorldOpenHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    /**
     * 数据库创建
     * <p>
     * 当第一次安装App的时候,且getReadableDatabase()方法第一次调用的时候,该方法被调用(只会执行一次).在这里创建数据表
     *
     * @param db 可执行 SQL 语句的对象
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
        //创建数据表的sql语句
        String sql = " CREATE TABLE " + TABLE_NAME + " ( "
                + KEY_ID + " INTEGER  PRIMARY KEY AUTOINCREMENT,"
                + KEY_NAME + " TEXT,"
                + KEY_AGE + " INTEGER,"
                + KEY_SEX + " TEXT,"
                + KEY_HEIGHT + " REAL" + ");";
        Log.d(TAG, "onCreate: " + sql);
        //执行sql语句
        db.execSQL(sql);
    }

    /**
     * 当更新App的时候,若版本发生改变,则该方法被调用.一般用于升级数据库.在这里做一些 修改表,创建表,删除表等操作.
     *
     * @param db         可执行 SQL 语句的对象
     * @param oldVersion 旧的版本号
     * @param newVersion 新的版本号
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.d(TAG, "onUpgrade: " + oldVersion + "," + newVersion);
        if (newVersion == 4) {
            //修改表的sql语句
            String sql = "alter table " + TABLE_NAME + " ADD " + KEY_SEX + " TEXT";
            Log.d(TAG, "onUpgrade: " + sql);
            //执行Sql 语句
            db.execSQL(sql);
        } else if (newVersion == 6) {
            //更新数据表的操作
            String tempTableName = "abc";
            //将原来的表名改为 abc
            db.execSQL("ALTER TABLE " + TABLE_NAME + " RENAME TO " + tempTableName);
            //创建新的表
            onCreate(db);
            //将 abc 的表中的数据 复制到 新表中
            db.execSQL("INSERT INTO TABLE_NAME(" + KEY_ID + "," + KEY_NAME + "," + KEY_AGE + "," + KEY_SEX + "," + KEY_HEIGHT + ") " +
                    "SELECT sId," + KEY_NAME + "," + KEY_AGE + "," + KEY_SEX + "," + KEY_HEIGHT + " FROM " + tempTableName);
            //删除 abc 表
            db.execSQL("DROP TABLE IF EXISTS " + tempTableName);
        }
    }

    /**
     * 新增数据
     *
     * @param stu
     * @return 返回熙增行的ID
     */
    public long insert(Student stu) {
        //获取数据库的可执行 SQL 语句的对象
        SQLiteDatabase db = getReadableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(KEY_NAME, stu.getsName());
        contentValues.put(KEY_AGE, stu.getsAge());
        contentValues.put(KEY_HEIGHT, stu.getHeight());
        contentValues.put(KEY_SEX, stu.getSex());
        contentValues.put(KEY_NAME, stu.getsName());
        long insert = db.insert(TABLE_NAME, null, contentValues);
        db.close();
        return insert;
    }


    /**
     * 删除 数据
     *
     * @param sId 需要删除的id
     */
    public int delete(int sId) {
        String sql = "DELETE FROM " + TABLE_NAME + " WHERE " + KEY_ID + " = " + sId;
        Log.d(TAG, "delete: " + sql);
        SQLiteDatabase db = getReadableDatabase();
        String[] whereArgs = {sId + ""};
        int delete = db.delete(TABLE_NAME, KEY_ID + " = ?", whereArgs);
        db.close();
        return delete;

    }

    /**
     * 修改
     *
     * @param updateId   修改的ID
     * @param newStudent 新的Student数据
     */
    public int update(int updateId, Student newStudent) {

        SQLiteDatabase db = getReadableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(KEY_NAME, newStudent.getsName());
        contentValues.put(KEY_AGE, newStudent.getsAge());
        contentValues.put(KEY_HEIGHT, newStudent.getHeight());
        contentValues.put(KEY_SEX, newStudent.getSex());
        contentValues.put(KEY_NAME, newStudent.getsName());
        int update = db.update(TABLE_NAME, null, KEY_ID + " = ? ", new String[]{updateId + ""});
        db.close();
        return update;
    }


    public List<Student> queryAll() {
        SQLiteDatabase db = getReadableDatabase();
        //查询的结果,里面记录了所有行和列(返回了一个查询结果表)
        //全查询
//        Cursor cursor = db.query(TABLE_NAME, null, null, null, null, null, null);
        String[] columns = {KEY_NAME, KEY_SEX, KEY_AGE, KEY_ID, KEY_HEIGHT};
        //查询指定的列,且有条件
        Cursor cursor = db.query(TABLE_NAME, columns, KEY_AGE + "> ? AND " + KEY_SEX + "=?", new String[]{"40", "女"}, null, null, null);
        if (cursor.getCount() > 0) {
            ArrayList<Student> students = new ArrayList<>();
            while (cursor.moveToNext()) {
                //获取字段值
                double height = cursor.getDouble(4);
                int id = cursor.getInt(3);
                int age = cursor.getInt(2);
                String name = cursor.getString(0);
                String sex = cursor.getString(1);
                Student student = new Student(id, age, name, height, sex);
                students.add(student);
            }
            return students;
        }
        cursor.close();//关闭游标,释放资源
        db.close();//关闭数据库链接对象,释放资源
        return null;
    }

    public Cursor queryAllReturnCursor() {
        SQLiteDatabase db = getReadableDatabase();
        return db.query(TABLE_NAME, null, null, null, null, null, null);
    }

}
